﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using DiscoveryLogic.Analytics.DataObjects;
using DiscoveryLogic.Common.Utilities;

namespace DiscoveryLogic.Analytics.BusinessLogic.Importers
{
    public class PublicationUtil
    {
        public static Dictionary<string, Journal> GetJournals(List<int> pmids)
        {
            Dictionary<string, Journal> journals = new Dictionary<string, Journal>();
            string connStr = ConfigurationManager.ConnectionStrings["WebOfScience"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string sql = @"select ISSN, Title from Journal a where ISSN in (
select ISSN from SourceIssue where SourceIssueID in (
select SourceIssueID from SourceItem where SourceItemID in (
select SourceItemId from Medline.dbo.CitationSourceItem where PMID={0})))
order by Year desc";
                foreach (int pmid in pmids)
                {
                    cmd.CommandText = string.Format(sql, pmid);
                    SqlDataReader reader = cmd.ExecuteReader();
                    if(reader.Read() && reader["ISSN"]!=DBNull.Value)
                    {
                        Journal journal = new Journal();
                        journal.JournalName = reader["Title"].ToString();
                        journal.ISSN = reader["ISSN"].ToString();
                        journal.SubjCategories=new List<string>();
                        if (!journals.ContainsKey(journal.ISSN))
                            journals.Add(journal.ISSN, journal);
                    }
                    reader.Close();
                }

                sql = @"
select SubjectCategoryId from SourceIssueSubjectCategory where SourceIssueID in (
select top 1 SourceIssueID from SourceIssue where ISSN='{0}' order by SourceIssueID desc)";
                foreach(string issn in journals.Keys)
                {
                    Journal journal = journals[issn];
                    cmd.CommandText = string.Format(sql, issn);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while(reader.Read())
                    {
                        string subjCategory = reader[0].ToString();
                        journal.SubjCategories.Add(subjCategory);
                    }
                    reader.Close();
                }
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return journals;
        }
    }
}
